﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient
Public Class FormapagDAL

    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub
#Region "FuncionesUtilizables"

    Public Function existeFormapag(ByVal pDesFormaP As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM formapagos " & _
                                            "WHERE DesFormaP = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("DesFormaP", pDesFormaP)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function existeFormapagID(ByVal pIdFormaP As Integer) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM formapagos " & _
                                            "WHERE IdFormaP = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("pIdFormaP", pIdFormaP)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraFormapag(ByVal pFormapag As FormapagBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO formapagos(IdEmpresa,DesFormaP, Status) " & _
                                            "VALUES (@IdEmpresa,@DesFormaP, @Status);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@IdEmpresa", 0)
                    myComm.Parameters.AddWithValue("@DesFormaP", pFormapag.DesFormaP)
                    myComm.Parameters.AddWithValue("@Status", "A")
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaFormapag(ByVal pFormapag As FormapagBE, ByRef msg As String) As Boolean
        Dim objCount As Int16
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " &
                                            "FROM formapagos " & _
                                            "WHERE IdFormaP <> @idFormaP " &
                                                "AND  DesFormaP = @DesFormaP;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@IdFormaP", pFormapag.IdFormaP)
                    myComm.Parameters.AddWithValue("@DesFormaP", pFormapag.DesFormaP)
                    myConn.Open()
                    objCount = Convert.ToInt16(myComm.ExecuteScalar())
                    If (objCount > 0) Then
                        msg &= "Existe otro registro con la Forma de Pago especificada." & vbCrLf
                        Return False
                    End If

                    myComm.CommandText = "UPDATE formapagos " & _
                                            "SET DesFormaP = @DesFormaP, " & _
                                                    "Status = @Status " & _
                                            "WHERE IdFormaP = @IdFormaP;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@DesFormaP", pFormapag.DesFormaP)
                    Select Case pFormapag.Status
                        Case FormapagBE.EstatusFormapag.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case FormapagBE.EstatusFormapag.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@IdFormaP", pFormapag.IdFormaP)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
            objCount = Nothing
        End Try
    End Function

    Public Function cambiaEstatus(ByVal pIdFormaPago As Integer, ByVal pSetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE formapagos " & _
                                            "SET Status = @Status " & _
                                            "WHERE IdFormaP = @IdFormaP;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@IdFormaP", pIdFormaPago)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getFormapag(ByVal pIdFormaP As Integer) As FormapagBE
        Dim tmpFormapag As FormapagBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdEmpresa, IdFormaP, DesFormaP, Status " & _
                                            "FROM formapagos " & _
                                            "WHERE IdFormaP = @IdFormaP;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdFormaP", pIdFormaP)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpFormapag = FormapagFact.Build(myDR)
                        Else
                            tmpFormapag = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpFormapag

        Catch ex As Exception
            Throw
        Finally
            tmpFormapag = Nothing
        End Try
    End Function

    Public Function getlistadoFormapagxtexto(ByVal pDesFormaP As String, ByVal tipoEstatus As Enumeraciones.estatusParaBusquedas) As List(Of FormapagBE)
        Dim FormapagLs As List(Of FormapagBE) = New List(Of FormapagBE)
        Dim qryEstatus As String = ""
        Select Case tipoEstatus
            Case Enumeraciones.estatusParaBusquedas.Todos
                'No se hace filtro
            Case Enumeraciones.estatusParaBusquedas.Activos
                qryEstatus = " AND Status = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                qryEstatus = " AND Status = 'I' "
        End Select

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdEmpresa, IdFormaP, DesFormaP, Status " & _
                                           "FROM formapagos " & _
                                            "WHERE DesFormaP LIKE @descripcion " & _
                                            qryEstatus & ";"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@descripcion", "%" & pDesFormaP & "%")
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Formapag As FormapagBE = FormapagFact.Build(myDR)
                            If (Not Formapag Is Nothing) Then
                                FormapagLs.Add(Formapag)
                            End If
                            Formapag = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return FormapagLs
        Catch ex As Exception
            Throw
        Finally
            FormapagLs = Nothing
            qryEstatus = Nothing
        End Try
    End Function
#End Region
    'Public Function registraFormapag(ByVal pFormapag As FormapagBE) As Boolean
    '    Dim instrSql As String
    '    Dim oInsParm(0 To 3) As OdbcParameter

    '    Try

    '        instrSql = "INSERT INTO ""Formapag""(""Descripcion"", ""Dias"", ""IdFormaP"", ""Status"") " & _
    '                                    "VALUES (?,?,?,?);"

    '        oInsParm(0) = New OdbcParameter("", pFormapag.Descripcion)
    '        oInsParm(1) = New OdbcParameter("", pFormapag.Dias)
    '        oInsParm(2) = New OdbcParameter("", pFormapag.IdFormaP)
    '        oInsParm(3) = New OdbcParameter("", pFormapag.Status)


    '        'Crea el objeto connection
    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.AddRange(oInsParm)
    '                myConn.Open()
    '                myComm.ExecuteNonQuery()
    '                If (myComm.ExecuteNonQuery() > 0) Then
    '                    Return True
    '                Else
    '                    Return False
    '                End If
    '            End Using
    '        End Using
    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        instrSql = Nothing
    '        oInsParm = Nothing
    '    End Try
    'End Function

    'Public Function modificaFormapag(ByVal pFormapag As FormapagBE) As Boolean
    '    Dim instrSql As String
    '    Dim oInsParm(0 To 3) As OdbcParameter
    '    Try
    '        instrSql = "UPDATE ""Formapag"" " & _
    '                                    "SET ""Descripcion""=?, ""Dias""=?, ""Status""=? " & _
    '                                            "WHERE ""IdFormaP"" = ?;"

    '        oInsParm(0) = New OdbcParameter("", pFormapag.Descripcion)
    '        oInsParm(1) = New OdbcParameter("", pFormapag.Dias)
    '        oInsParm(2) = New OdbcParameter("", pFormapag.Status)
    '        oInsParm(3) = New OdbcParameter("", pFormapag.IdFormaP)

    '        'Crea el objeto connection
    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.AddRange(oInsParm)
    '                myConn.Open()
    '                myComm.ExecuteNonQuery()
    '                Return True
    '            End Using
    '        End Using

    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        instrSql = Nothing
    '        oInsParm = Nothing
    '    End Try
    'End Function

    'Public Function getFormapag(ByVal pIdEmpr As Integer, ByVal pStrFormapag As String) As List(Of FormapagBE)
    '    Dim FormapagLs As List(Of FormapagBE) = New List(Of FormapagBE)
    '    Dim instrSql As String

    '    Try
    '        instrSql = "SELECT ""Descripcion"", ""Dias"", ""IdFormaP"", ""Status"" " & _
    '                                 "FROM ""Formapag"" " & _
    '                                "WHERE ""IdFormaP"" = ? " & _
    '                                    "AND ""Descripcion"" LIKE '%" & pStrFormapag & "%' ;"

    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
    '                myConn.Open()
    '                Using myDR As OdbcDataReader = myComm.ExecuteReader()
    '                    While myDR.Read()
    '                        'Dim Formapag As FormapagBE = FormapagFact.Build(myDR)
    '                        'If (Not Formapag Is Nothing) Then
    '                        '    FormapagLs.Add(Formapag)
    '                        'End If
    '                        'Formapag = Nothing
    '                    End While
    '                End Using
    '            End Using
    '        End Using
    '        Return FormapagLs
    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        FormapagLs = Nothing
    '        instrSql = Nothing
    '    End Try
    'End Function
End Class
